package org.nahtanoj.contacts.sheet;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import org.nahtanoj.driveutils.Authenticator;

import com.google.gdata.client.docs.DocsService;
import com.google.gdata.client.spreadsheet.CellQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.Link;
import com.google.gdata.data.PlainTextConstruct;
import com.google.gdata.data.batch.BatchOperationType;
import com.google.gdata.data.batch.BatchStatus;
import com.google.gdata.data.batch.BatchUtils;
import com.google.gdata.data.spreadsheet.Cell;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

/* Wrapper class around the google spreadsheet api to make modification easier. */
public class Sheet implements java.io.Serializable {
	/**
	 * A basic struct to store cell row/column information and the associated RnCn
	 * identifier.
	 */
	public class CellAddress {
		public final int col;
		public final String idString;
		public final int row;
		public final String val;

		/**
		 * Constructs a CellAddress representing the specified {@code row} and
		 * {@code col}. The idString will be set in 'RnCn' notation.
		 * @param col
		 * @param row
		 * @param val
		 */
		public CellAddress(int col, int row, String val) {
			this.col = col;
			this.row = row;
			this.val = val;
			this.idString = String.format("R%sC%s", row, col);
		}
	}

	private static final long serialVersionUID = -3678548207727543697L;
	private static final int BATCHSIZE = 250;
	private static final Logger log = Logger.getLogger(Sheet.class.getName());

	private List<CellAddress> cellAddrs = new ArrayList<CellAddress>();
	private final DocsService client = new DocsService("contactSync");
	private ColumnHeadings columnHeadings;
	private final SpreadsheetService myService = new SpreadsheetService("contactSync");
	private SpreadsheetEntry sheet;
	private String sheetName;

	private WorksheetEntry tab;

	public Sheet(String user, String sheet, String tab, int headingRow) throws AuthenticationException {
		authenticate(user, sheet, tab);
		loadColumns(headingRow);
	}

	/**
	 * If named sheet exist then it is selected.
	 * Otherwise a new sheet is created with the name given.
	 * @param s Spreadsheet to find or create
	 * @param t tab to find
	 * @throws AuthenticationException
	 */
	private void authenticate(String u, String s, String t) {
		Authenticator a = new Authenticator(u);
		this.sheetName = s;
		this.myService.setOAuth2Credentials(a.getCredential());
		this.myService.setConnectTimeout(500000);
		this.myService.setReadTimeout(500000);
		this.myService.setProtocolVersion(SpreadsheetService.Versions.V3);
		setSheet(s, t);
	}

	//	private void authenticate2(String s, String t) throws AuthenticationException {
	//		java.io.File keyFile = new java.io.File("service-key.p12");
	//		if (!keyFile.exists()) {
	//			keyFile = new java.io.File("war/service-key.p12");
	//		}
	//		try {
	//			GoogleCredential credentials = new GoogleCredential.Builder()
	//			.setTransport(HTTP_TRANSPORT)
	//			.setJsonFactory(JSON_FACTORY)
	//			.setServiceAccountId("934792418145@developer.gserviceaccount.com")
	////			.setServiceAccountScopes("https://spreadsheets.google.com/feeds/")
	//			.setServiceAccountPrivateKeyFromP12File(keyFile).build();
	//			new GoogleCredential.Builder().setTransport(HTTP_TRANSPORT)
	//			.setJsonFactory(JSON_FACTORY)
	//			.setRequestInitializer(credentials)
	//			.build();
	//	        this.sheetName = s;
	//	        this.myService.setOAuth2Credentials(credentials);
	//			this.myService.setConnectTimeout(500000);
	//			this.myService.setReadTimeout(500000);
	//			this.myService.setProtocolVersion(SpreadsheetService.Versions.V3);
	////			this.myService.setUserCredentials(u + "@gmail.com", p);
	////			this.myService.setProtocolVersion(SpreadsheetService.Versions.V1);
	////			this.client.setUserCredentials(u + "@gmail.com", p);
	////			this.client.setProtocolVersion(DocsService.Versions.V2);
	//			setSheet(s, t);
	//		} catch (GeneralSecurityException e) {
	//			log.warning("GeneralSecurityException:" + e.getLocalizedMessage());
	//		} catch (IOException e) {
	//			log.warning("IOException:" + e.getLocalizedMessage());
	//		}
	//	}

	/**
	 * Uses a batch request to retrieve a {@link CellEntry} for each cell enumerated in
	 * {@code cellAddrs}. Each cell entry is placed into a map keyed by its RnCn
	 * identifier.
	 *
	 * @return a map consisting of one {@link CellEntry} for each address in {@code cellAddrs}
	 */
	public Map<String, CellEntry> getCellEntryMap() throws IOException, ServiceException {
		Map<String, CellEntry> cellEntryMap = new HashMap<String, CellEntry>(this.cellAddrs.size());
		CellFeed batchRequest = new CellFeed();
		for (CellAddress cellId : this.cellAddrs) {
			CellEntry batchEntry = new CellEntry(cellId.row, cellId.col, cellId.idString);
			batchEntry.setId(String.format("%s/%s", this.tab.getCellFeedUrl().toString(), cellId.idString));
			BatchUtils.setBatchId(batchEntry, cellId.idString);
			BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.QUERY);
			batchRequest.getEntries().add(batchEntry);
		}
		CellFeed cellFeed = null;
		try {
			cellFeed = this.myService.getFeed(this.tab.getCellFeedUrl(), CellFeed.class);
		} catch (IOException e) {
			log.warning("IOException:" + e.getMessage());
		}
		CellFeed queryBatchResponse = null;
		try {
			if (cellFeed != null) {
				queryBatchResponse = this.myService.batch(new URL(cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM).getHref()), batchRequest);
			}
		} catch (IOException e) {
			log.warning("IOException:" + e.getMessage());
		}
		if (queryBatchResponse != null) {
			for (CellEntry entry : queryBatchResponse.getEntries()) {
				cellEntryMap.put(BatchUtils.getBatchId(entry), entry);
			}
		}
		return cellEntryMap;
	}

	/**
	 * Wrapper to retrieve a cells content by input value - returns a formula if entered
	 * return cell content if formula null
	 * @param row
	 * @param col
	 * @return the plain text value of the cell at row and col or empty string
	 */
	private String getChooseValue(int col, int row, boolean inputValue) {
		String val = "";
		if (this.tab.getRowCount() >= row) {
			try {
				CellQuery query = new CellQuery(this.tab.getCellFeedUrl());
				query.setMinimumRow(row);
				query.setMaximumRow(row);
				query.setMinimumCol(col);
				query.setMaximumCol(col);
				CellFeed feed = this.myService.query(query, CellFeed.class);
				if (feed.getEntries().size() == 1) {
					if (inputValue == true) {
						val = feed.getEntries().get(0).getCell().getInputValue();
					} else {
						val = feed.getEntries().get(0).getCell().getValue();
					}
				}
			} catch (IOException e) {
				log.warning("Sheet getValue: IOException:" + e.getMessage());
			} catch (ServiceException e) {
				log.warning("Sheet getValue: ServiceException:" + e.getMessage());
			}
		}
		return val;
	}

	public int getColCount() {
		return this.tab.getColCount();
	}


	public String getInputValue(int col, int row) {
		return getChooseValue(col, row, true);
	}

	/**
	 * Wrapper to retrieve a cells content by input value - returns a formula if entered
	 * @param row
	 * @param col
	 * @return the plain text value of the cell at row and col or empty string
	 */

	public String getInputValue(int col, String row) {
		return getInputValue(col, Integer.parseInt(row));
	}

	/**
	 * Return cell contents from named column and row
	 * @param col - named column from headings
	 * @param row - row
	 * @return content of cell
	 */
	public String getInputValue(String col, int row) {
		return getInputValue(this.columnHeadings.get(col), row);
	}

	public int getRowCount() {
		return this.tab.getRowCount();
	}

	public String getValue(int col, int row) {
		return getChooseValue(col, row, false);
	}

	public String getValue(String col, int row) {
		return getChooseValue(this.columnHeadings.get(col), row, false);
	}

	/**
	 * Wrapper to retrieve a cell array
	 * @param row starting row
	 * @param col starting column
	 * @param rows number of rows
	 * @param cols number of columns
	 * @return a string array with the plain text value of the cell at row and col or empty string
	 */
	public String[][] getValues(int col, int row, int cols, int rows) {
		int colMax = this.tab.getColCount();
		int rowMax = this.tab.getRowCount();
		int width = col + cols - 1;
		int height = row + rows - 1;
		if (width > colMax) {
			width = colMax - col + 1;
		} else {
			width = cols;
		}
		if (height > rowMax) {
			height = rowMax - row + 1;
		} else {
			height = rows;
		}
		String [][]values = new String[width][height];
		if (rowMax >= row && colMax >= col && width > 0 && height > 0) {
			try {
				CellQuery query = new CellQuery(this.tab.getCellFeedUrl());
				query.setMinimumRow(row);
				query.setMaximumRow(row + height - 1);
				query.setMinimumCol(col);
				query.setMaximumCol(col + width - 1);
				CellFeed feed = this.myService.query(query, CellFeed.class);
				for (CellEntry e : feed.getEntries()) {
					Cell c = e.getCell();
					values[c.getCol() - col][c.getRow() - row] = c.getValue();//c.getInputValue();
				}
			} catch (IOException e) {
				log.warning("Sheet getValues: IOException:" + e.getMessage());
			} catch (ServiceException e) {
				log.warning("Sheet getValues: ServiceException:" + e.getMessage());
			}
		}
		return values;
	}

	public String[][] getValues(String col, int row, int cols, int rows) {
		return getValues(this.columnHeadings.get(col), row, cols, rows);
	}

	public String[][] getValues(String col, int row, String lastCol, int colCount) {
		return getValues(col, row, this.columnHeadings.get(lastCol), colCount);
	}
	private void loadColumns(int row) {
		this.columnHeadings = new ColumnHeadings(this, row);
	}

	private void setRows(int rows) {
		try {
			WorksheetEntry ws = this.myService.getFeed(this.sheet.getWorksheetFeedUrl(), WorksheetFeed.class).getEntries().get(0);
			ws.setRowCount(rows);
			ws.update();
			/* Refresh the local reference */
			setSheet(this.sheetName, this.tab.getTitle().getPlainText());
		} catch (AuthenticationException e) {
			log.warning("Sheet setRows: AuthenticationException:" + e.getMessage());
		} catch (MalformedURLException e) {
			log.warning("Sheet setRows: MalformedURLException:" + e.getMessage());
		} catch (IOException e) {
			log.warning("Sheet setRows: IOException:" + e.getMessage());
		} catch (ServiceException e) {
			log.warning("Sheet setRows: ServiceException:" + e.getMessage());
		}
	}

	private void setSheet(String s, String t) {
		try {
			SpreadsheetQuery query = new SpreadsheetQuery(new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full"));
			query.setTitleQuery(s);
			query.setTitleExact(true);
			query.setMaxResults(1);
			SpreadsheetFeed feed = this.myService.getFeed(query, SpreadsheetFeed.class);
			List<SpreadsheetEntry> spreadsheets = feed.getEntries();
			if (spreadsheets.size() == 1) {
				this.sheet = spreadsheets.get(0);
				// Default to first tab - or named tab if it exists
				WorksheetEntry def = this.sheet.getWorksheets().get(0);
				this.tab = null;
				for (WorksheetEntry e : this.sheet.getWorksheets()) {
					if (e.getTitle().getPlainText().equals(t)) {
						this.tab = e;
					}
				}
				if (this.tab == null) {
					this.tab = def;
					log.warning("Using default sheet:" + this.tab.getTitle().getPlainText());
				}
			} else {
				/* Need to create a sheet */
				com.google.gdata.data.docs.SpreadsheetEntry newEntry = new com.google.gdata.data.docs.SpreadsheetEntry();
				newEntry.setTitle(new PlainTextConstruct(s));
				this.client.insert(new URL("https://docs.google.com/feeds/documents/private/full/"), newEntry);
				setSheet(s, t);
			}
		} catch (MalformedURLException e) {
			log.warning("Sheet setSheet: MalformedURLException:" + e.getMessage());
		} catch (IOException e) {
			log.warning("Sheet setSheet: IOException:" + e.getMessage());
		} catch (ServiceException e) {
			log.warning("Sheet setSheet: ServiceException:" + e.getMessage());
		}
	}

	/**
	 * Inserts text at row and column given in given tab
	 * @param r Row
	 * @param c Column
	 * @param v Value
	 */
	public void setValue(int c, int r, String v) {
		if (this.tab.getRowCount() < r) {
			setRows(r + 10);
		}
		if (this.cellAddrs.size() == BATCHSIZE) {
			submit();
		}
		this.cellAddrs.add(new CellAddress(c, r, v));
	}
	public void setValue(Integer c, String row, String v) {
		setValue(c, Integer.parseInt(row), v);
	}

	/**
	 * Set cell in named column and specified row to value
	 * @param c - column name from headings scanned
	 * @param r - row number
	 * @param v - value
	 */
	public void setValue(String c, int r, String v) {
		setValue(this.columnHeadings.get(c), r, v);
	}

	// Note sometimes the WebRequest/WebResponse will bomb out with premature EOF
	public void submit() {
		if (this.cellAddrs.size() > 0) {
			try {
				Map<String, CellEntry> cellEntries = getCellEntryMap();
				if (cellEntries != null) {
					CellFeed batchRequest = new CellFeed();
					for (CellAddress cellAddr : this.cellAddrs) {
						CellEntry batchEntry = new CellEntry(cellEntries.get(cellAddr.idString));
						batchEntry.changeInputValueLocal(cellAddr.val);
						BatchUtils.setBatchId(batchEntry, cellAddr.idString);
						BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE);
						batchRequest.getEntries().add(batchEntry);
					}
					CellFeed cellFeed = this.myService.getFeed(this.tab.getCellFeedUrl(), CellFeed.class);
					Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
					CellFeed batchResponse = null;
					try {
						this.myService.setHeader("If-Match", "*");
						batchResponse = this.myService.batch(new URL(batchLink.getHref()), batchRequest);
						this.myService.setHeader("If-Match", null);
						// Check the results
						for (CellEntry entry : batchResponse.getEntries()) {
							String batchId = BatchUtils.getBatchId(entry);
							if (!BatchUtils.isSuccess(entry)) {
								BatchStatus status = BatchUtils.getBatchStatus(entry);
								log.warning("Sheet submit: " + batchId + " failed (" + status.getReason() + ") " + status.getContent());
							}
						}
						this.cellAddrs = new ArrayList<CellAddress>();
					} catch (com.google.gdata.client.batch.BatchInterruptedException e) {
						log.warning("Sheet submit: Interrupted:" + e.getMessage());
					}
				} else {
					log.warning("Failed to process:null cellEntries");
				}
			} catch (ServiceException e) {
				log.warning("Sheet submit: ServiceException:" + e.getMessage());
			} catch (IOException e) {
				log.warning("Sheet submit: IOException:" + e.getMessage());
			} catch (NullPointerException e) {
				log.warning("Sheet submit: NullPointerException:" + e.getMessage());
			}
		}
	}
}
